﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class ScreenCustomerDal
    {
        public ILog log { get; set; }
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 根据筛查ID,客户ID查询筛查客户对像视图
        /// 原程序 ScreeningServiceImpl-> selectScCustomer(String scid, String customerid)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="customerId">潜客ID</param>
        /// <returns></returns>
        public ScreenCustomerView GetScreenCustomerByScreenIdAndCustomerId(Guid scid, Guid customerId)
        {
            return dapperHelper.ExecueWithoutTransaction<ScreenCustomerView>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,D.CreatedOn reservationCreatedon,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE A.NEW_CUSTOMERID = '{customerId}' and A.NEW_SCID = '{scid}' and A.statecode = 0  ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndCustomerId 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                var views = a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
                return (views != null && views.Count() > 0) ? views.OrderBy(g => g.reservationCreatedon).FirstOrDefault() : null;
            });
        }

        /// <summary>
        /// 根据筛查ID查询进行中的筛查客户对像视图
        /// 原程序 ScreeningServiceImpl-> selectScCustomerNotCompleteByScid(String scid)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerNotCompleteByScreenId(Guid scid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}' and a.new_scstate!= 1 AND A.statecode=0 ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerNotCompleteByScreenId 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                IEnumerable<ScreenCustomerView> views = a.Query<ScreenCustomerView>(sqlTemplate.RawSql);

                if (views != null && views.Count() > 0) return views.OrderByDescending(g => g.createdon).ToList();

                return null;
            });
        }

        /// <summary>
        /// 根据筛查ID查询所有筛查客户对像视图
        /// 原程序 ScreeningServiceImpl-> selectScCustomerAllState(String scid)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetAllScreenCustomerByScreenId(Guid scid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND A.statecode=0 ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetAllScreenCustomerByScreenId 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                IEnumerable<ScreenCustomerView> views = a.Query<ScreenCustomerView>(sqlTemplate.RawSql);

                if (views != null && views.Count() > 0) return views.OrderByDescending(g => g.createdon).ToList();

                return null;
            });
        }

        /// <summary>
        /// 根据筛查ID和状态查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectScCustomerByScidAndState(String scid, String scstate)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndState(Guid scid, int scstate)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND  new_scstate={scstate}  AND A.statecode=0 ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndState 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态为进行中,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectScCustomerByScidAndKey(String scid, String key) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="citizenid">身份证</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndCitizenId(Guid scid, string citizenid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND  new_scstate=0  AND A.statecode=0
		                        And CHARINDEX('{citizenid}', b.new_citizenid) > 0  ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndCitizenId 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态为进行中,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectScCustomerByScidAndKey(String scid, String key) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="phone">手机号</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndPhone(Guid scid, string phone)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.description  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND  new_scstate=0  AND A.statecode=0
	                            AND  CHARINDEX('{phone}', b.new_phone) > 0 ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndPhone 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态为进行中,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表，姓名与班级号没有办法分辨，所以两者都查询
        /// 原程序 ScreeningServiceImpl-> selectScCustomerByScidAndKey(String scid, String key) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="key">姓名或筛查号</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndFullNameClassNumber(Guid scid, string key)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.description  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                               c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND  new_scstate=0  AND A.statecode=0
	                            AND CHARINDEX('{key}', c.new_classnumber) > 0 
                                union
                                select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,a.new_scid,B.LEADID LEADID,
                                b.description  remark, b.FullName customername,b.new_age age,b.new_gender sex,
                                b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                c.new_classnumber classnum,D.new_reservationID reservationId,D.new_reservationdate reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND  new_scstate=0  AND A.statecode=0
	                            AND CHARINDEX('{key}', b.FullName) > 0 ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndFullNameClassNumber 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectByScidStateAndKey(String scid, Integer scstate, String key, int fromIndex, int toIndex) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="citizenid">身份证</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndSCStaceAndCitizenidPaging(Guid scid, int scstate, string citizenid, int pageIndex, int pageSize)
        {
            int skipIndex = (pageIndex - 1) * (pageSize - 1);

            string sqlcmd = $@"select a.*,D.new_reservationID reservationId,dbo.fn_UTCToTzCodeSpecificLocalTime(D.new_reservationdate,220) reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                    from(
                                        select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                        a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                        a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,B.LEADID LEADID,
                                        b.description  remark, b.FullName customername,b.new_age age,b.new_gender sex,a.new_scid,
                                        b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                        b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                        b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                        c.new_classnumber classnum,--D.new_reservationID reservationId,dbo.fn_UTCToTzCodeSpecificLocalTime(D.new_reservationdate,220) reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                        (select top 1 D.new_reservationID
									        from new_reservationbase D where D.new_lead = A.new_customerid and D.new_screen = A.new_scid order by d.new_reservationdate desc
								        ) as reservationId
                                        FROM dbo.new_sc_customerBase A with(nolock)
                                        LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                        LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                        --LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                        WHERE  A.NEW_SCID = '{scid}'   AND A.statecode=0
	                                    AND CHARINDEX('{citizenid}', b.new_citizenid) > 0 
                                    ) as a left join new_reservationbase D with(nolock) ON  a.reservationId=D.new_reservationid where 1=1 ";
            string conditionSql = string.Empty;

            //根据筛查状态查询
            if (scstate == 0 || scstate == 1)
            {
                conditionSql = $@" AND a.new_scstate={scstate}  ";
            }//根据预约状态查询
            else if (scstate == 2 || scstate == 3)
            {
                string reservationSql = scstate == 2 ? "  D.new_reservationID is null" : "  D.new_reservationID is not null ";
                conditionSql = $@" and {reservationSql}";
            }

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                sqlcmd = string.Concat(sqlcmd, conditionSql, $" ORDER BY a.createdon DESC OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only");
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndSCStaceAndCitizenidPaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectByScidStateAndKey(String scid, Integer scstate, String key, int fromIndex, int toIndex) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="citizenid">身份证</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public int GetCountScreenCustomerByScreenIdAndSCStaceAndCitizenidPaging(Guid scid, int scstate, string citizenid)
        {
            string sqlcmd = $@"select count(distinct(new_sc_customerid))
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND A.statecode=0
	                            AND CHARINDEX('{citizenid}', b.new_citizenid) > 0  ";

            string conditionSql = string.Empty;
            if (scstate == 1 || scstate == 0)
            {
                conditionSql = " AND  new_scstate= " + scstate;
            }
            else if (scstate == 2 || scstate == 3)
            {
                conditionSql = scstate == 2 ? " and D.new_reservationID is null" : " and D.new_reservationID is not null ";
            }
            if (conditionSql != string.Empty)
                sqlcmd = string.Concat(sqlcmd, conditionSql);

            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetCountScreenCustomerByScreenIdAndSCStaceAndCitizenidPaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });


        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectByScidStateAndKey(String scid, Integer scstate, String key, int fromIndex, int toIndex) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="phone">电话</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndSCStaceAndPhonePaging(Guid scid, int scstate, string phone, int pageIndex, int pageSize)
        {
            int skipIndex = (pageIndex - 1) * (pageSize - 1);

            ////有多条预约时会显示重复的客户列表 louis updatedon 2019-11-5
            string sqlcmd = $@"select a.*,D.new_reservationID reservationId,dbo.fn_UTCToTzCodeSpecificLocalTime(D.new_reservationdate,220) reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt 
                                        FROM (
                                        select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                        a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                        a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,B.LEADID LEADID,
                                        b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,a.new_scid,
                                        b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                        b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                        b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                        c.new_classnumber classnum,
                                        (select top 1 D.new_reservationID
									        from new_reservationbase D where D.new_lead = A.new_customerid and D.new_screen = A.new_scid order by d.new_reservationdate desc
								        ) as reservationId
                                        FROM dbo.new_sc_customerBase A with(nolock)
                                        LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                        LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                        LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                        WHERE  A.NEW_SCID = '{scid}'   AND A.statecode=0
	                                    AND CHARINDEX('{phone}', b.new_phone) > 0 
                                        ) as a left join new_reservationbase D with(nolock) ON  a.reservationId=D.new_reservationid where 1=1 ";

            string conditionSql = string.Empty;

            //根据筛查状态查询
            if (scstate == 0 || scstate == 1)
            {
                conditionSql = $@" AND a.new_scstate={scstate}  ";
            }//根据预约状态查询
            else if (scstate == 2 || scstate == 3)
            {
                string reservationSql = scstate == 2 ? "  D.new_reservationID is null" : "  D.new_reservationID is not null ";
                conditionSql = $@" and {reservationSql}";
            }

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                sqlcmd = string.Concat(sqlcmd, conditionSql, $" ORDER BY a.createdon DESC OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only");
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndSCStaceAndPhonePaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectByScidStateAndKey(String scid, Integer scstate, String key, int fromIndex, int toIndex) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="phone">电话</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public int GetCountScreenCustomerByScreenIdAndSCStaceAndPhonePaging(Guid scid, int scstate, string phone)
        {
            
                string sqlcmd = $@"
                                select count(distinct(new_sc_customerid))
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND A.statecode=0
	                            AND CHARINDEX('{phone}', b.new_phone) > 0  ";

            string conditionSql = string.Empty;
            if (scstate == 1 || scstate == 0)
            {
                conditionSql = " AND  new_scstate= " + scstate;
            }
            else if (scstate == 2 || scstate == 3)
            {
                conditionSql = scstate == 2 ? " and D.new_reservationID is null" : " and D.new_reservationID is not null ";
            }
            if (conditionSql != string.Empty)
                sqlcmd = string.Concat(sqlcmd, conditionSql);

            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetCountScreenCustomerByScreenIdAndSCStaceAndPhonePaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectByScidStateAndKey(String scid, Integer scstate, String key, int fromIndex, int toIndex) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="key">筛查ID</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndSCStaceAndFullNameClassNumberPaging(Guid scid, int scstate, string key, int pageIndex, int pageSize)
        {
            int skipIndex = (pageIndex - 1) * (pageSize - 1);
            string sqlcmd = $@"select a.*,D.new_reservationID reservationId,dbo.fn_UTCToTzCodeSpecificLocalTime(D.new_reservationdate,220) reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt
                                   FROM (select  a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                   a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                   a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,B.LEADID LEADID,
                                   b.DESCRIPTION  remark, b.FullName customername,b.new_age age,b.new_gender sex,a.new_scid,
                                   b.new_citizenid sfz,b.Address1_Name [address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                   b.Telephone2 telno,b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                   b.new_wechat weixinno, b.new_alias alias,b.new_birthdate birthdate,b.ADDRESS1_TELEPHONE3 parentphone,
                                   c.new_classnumber classnum ,
                                   (select top 1 D.new_reservationID
                                        from new_reservationbase D where D.new_lead = A.new_customerid and D.new_screen = A.new_scid order by d.new_reservationdate desc
                                        ) as reservationId
                                   FROM dbo.new_sc_customerBase A with(nolock)
                                   LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                   LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                   --LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                   WHERE  A.NEW_SCID = '{scid}' AND A.statecode=0
                                AND (CHARINDEX('{key}', c.new_classnumber) > 0 OR  CHARINDEX('{key}', b.fullname) > 0  )
                                   ) as a left join new_reservationbase D with(nolock) ON  a.reservationId=D.new_reservationid where 1=1 ";
            string conditionSql = string.Empty;

            //根据筛查状态查询
            if (scstate == 0 || scstate == 1)
            {
                conditionSql = $@" AND a.new_scstate={scstate}  ";
            }//根据预约状态查询
            else if (scstate == 2 || scstate == 3)
            {
                string reservationSql = scstate == 2 ? "  D.new_reservationID is null" : "  D.new_reservationID is not null ";
                conditionSql = $@" and {reservationSql}";
            }

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                sqlcmd = string.Concat(sqlcmd, conditionSql, $" ORDER BY a.createdon DESC OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only");
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndSCStaceAndCitizenidPaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
            
        }

        /// <summary>
        /// 根据筛查ID和客户筛查状态,(姓名,电话,身份证,学号模糊查询)查询筛查客户列表
        /// 原程序 ScreeningServiceImpl-> selectByScidStateAndKey(String scid, Integer scstate, String key, int fromIndex, int toIndex) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="key">筛查ID</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public int GetCountScreenCustomerByScreenIdAndSCStaceAndFullNameClassNumberPaging(Guid scid, int scstate, string key)
        {
            string sqlcmd = $@" select count(distinct(new_sc_customerid))
                                FROM dbo.new_sc_customerBase A with(nolock)
                                LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                WHERE  A.NEW_SCID = '{scid}'  AND A.statecode=0
                             AND (CHARINDEX('{key}', c.new_classnumber) > 0 OR  CHARINDEX('{key}', b.fullname) > 0 )                            
                                ";
            string conditionSql = string.Empty;
            if (scstate == 1 || scstate == 0)
            {
                conditionSql = " AND  new_scstate= " + scstate;
            }
            else if (scstate == 2 || scstate == 3)
            {
                conditionSql = scstate == 2 ? " and D.new_reservationID is null" : " and D.new_reservationID is not null ";
            }
            if (conditionSql != string.Empty)
                sqlcmd = string.Concat(sqlcmd, conditionSql);

            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetCountScreenCustomerByScreenIdAndSCStaceAndPhonePaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID与筛查客户状态查询筛查客户信息
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByScreenIdAndSCStatePaging(Guid scid, int scstate, int pageIndex, int pageSize)
        {
            int skipIndex = (pageIndex - 1) * (pageSize - 1);
            string sqlcmd = $@" select *,D.new_reservationID reservationId,dbo.fn_UTCToTzCodeSpecificLocalTime(D.new_reservationdate,220) reservationdate ,D.new_source,D.new_screen,D.new_mobile,D.new_hospital,D.new_department,D.new_type,D.new_contnt from 
                                  ( select a.new_customerid,a.OwnerId,a.createdon,B.new_insurancetype,b.new_disease,b.new_insuranceid,
                                    a.new_meringflag,a.new_school,a.new_scnum,a.new_scitemid,a.new_screenteamid,
                                    a.new_scstate,a.new_reportcount,a.new_sc_customerId sc_customerid,B.LEADID LEADID,
                                    b.DESCRIPTION remark, b.FullName customername, b.new_age age, b.new_gender sex,a.new_scid,
                                    b.new_citizenid sfz, b.Address1_Name[address],b.Address1_Telephone1 iphoneno1, b.new_mobile iphoneno2,
                                    b.Telephone2 telno, b.Address1_Telephone2 telno2, b.EMailAddress1 email, b.new_qq qq,
                                    b.new_wechat weixinno, b.new_alias alias, b.new_birthdate birthdate, b.ADDRESS1_TELEPHONE3 parentphone,
                                    c.new_classnumber classnum,
                                    (select top 1 D.new_reservationID
									from new_reservationbase D where D.new_lead = A.new_customerid and D.new_screen = A.new_scid order by d.new_reservationdate desc
									) as reservationId
                                    FROM  dbo.new_sc_customerBase A with(nolock)
                                    LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID
                                    LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID
                                 
                                    WHERE  A.NEW_SCID = '{scid}'  AND A.statecode=0
                                  ) 
                                  as a left join  new_reservationbase D with(nolock) ON  a.reservationId=D.new_reservationid where 1=1
                                  ";
            string conditionSql = string.Empty;

            //根据筛查状态查询
            if (scstate == 0 || scstate == 1)
            {
                conditionSql = $@" AND a.new_scstate={scstate}  ";
            }//根据预约状态查询
            else if (scstate == 2 || scstate == 3)
            {
                string reservationSql = scstate == 2 ? "  D.new_reservationID is null" : "  D.new_reservationID is not null ";
                conditionSql = $@" and {reservationSql}";
            }

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                sqlcmd = string.Concat(sqlcmd, conditionSql, $" ORDER BY a.createdon DESC OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only");
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByScreenIdAndSCStatePaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据筛查ID与筛查客户状态查询筛查客户信息
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="scstate">筛查客户状态</param>
        /// <returns></returns>
        public int GetCountScreenCustomerByScreenIdAndSCStatePaging(Guid scid, int scstate)
        {
            //有多条预约时会显示重复的客户列表 louis updatedon 2019-11-5
            string sqlcmd = $@" select count(distinct( a.new_sc_customerId))
                                    FROM  dbo.new_sc_customerBase A with(nolock)
                                    LEFT JOIN dbo.Lead B with(nolock) ON A.NEW_CUSTOMERID = B.LEADID 
                                    LEFT JOIN dbo.new_classBase C with(nolock) ON B.NEW_CLASS = C.NEW_CLASSID 
                                    LEFT JOIN new_reservationbase D with(nolock) ON D.new_lead = A.new_customerid and D.new_screen = A.new_scid
                                    WHERE  A.NEW_SCID = '{scid}'  AND A.statecode=0";
            string conditionSql = string.Empty;
            if (scstate == 1 || scstate == 0)
            {
                conditionSql = " AND  new_scstate= " + scstate;
            }
            else if (scstate == 2 || scstate == 3)
            {
                conditionSql = scstate == 2 ? " and D.new_reservationID is null" : " and D.new_reservationID is not null ";
            }
            if (conditionSql != string.Empty)
                sqlcmd = string.Concat(sqlcmd, conditionSql);

            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sqlcmd);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetCountScreenCustomerByScreenIdAndSCStatePaging 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查客户ID查询筛查客户
        /// 原程序 ScreeningServiceImpl-> selectScCustomer(String customerId)
        /// </summary>
        /// <param name="customerid">筛查客户ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerByCustomerId(Guid customerid)
        {

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select new_customerid,new_scid, new_scstate, 
                                new_reportcount, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, 
                                new_screenteamid, new_scitemid, new_scnum 
                                from new_sc_customerbase with(nolock) where new_customerid ='{customerid}'";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerByCustomerId 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查编号,客户名称查询筛查报告
        /// 原程序 ScreeningServiceImpl-> selectScCustomerByscNum(String scNum, String scName)
        /// </summary>
        /// <param name="scnum">筛查编号</param>
        /// <param name="fullname">客户名称</param>
        /// <returns></returns>
        public IEnumerable<ScreenCustomerView> GetScreenCustomerBySCNum(string scnum, string fullname)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenCustomerView>>(a =>
            {
                string sql = $@"select new_customerid,new_scid, new_scstate, 
                                new_reportcount, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, 
                                new_screenteamid, new_scitemid, new_scnum 
                                from dbo.new_sc_customerbase t with(nolock)
	                            where t.new_scnum='{scnum}' and exists(select 1 from dbo.LeadBase b  with(nolock) where t.NEW_CUSTOMERID=b.leadid and b.fullname='{fullname}')
		                        order by t.createdon desc";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerBySCNum 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<ScreenCustomerView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查编号,客户名称查询重名客户数量
        /// 原程序 ScreeningServiceImpl-> countCustomernameByScid(String scid, String customername)
        /// </summary>
        /// <param name="scnum">筛查编号</param>
        /// <param name="fullname">客户名称</param>
        /// <returns></returns>
        public int GetCountScreenCustomerByScidAndFullName(string scid, string fullname)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                string sql = $@"select count(b.lastname) from dbo.new_sc_customerbase c 
	                            LEFT JOIN dbo.Leadbase b ON c.new_customerid = b.leadid
	                            where c.new_scid='{scid}' AND b.fullname = '{fullname}'
	                            and c.statecode=0";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetCountScreenCustomerBySCNumAndFullName 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.QueryFirst<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 查询筛查完成的客户id
        /// 原程序 ScreeningServiceImpl-> selectFinshedCustomerId(String school, String classnum) 
        /// </summary>
        /// <param name="school">学校ID</param>
        /// <param name="classnum">班级号</param>
        /// <returns></returns>
        public IEnumerable<dynamic> GetScreenCustomerBySchoolAndClassNum(Guid school, string classnum)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<dynamic>>(a =>
            {
                string sql = $@"select distinct
                            sc.new_customerid from new_sc_customerBase sc
                            left join LeadBase l on l.LeadId = sc.new_customerid
                            left join new_classBase c on c.new_classId = l.new_class
                            where l.new_school='{school}' and sc.new_scstate = 1  and charindex('{classnum}',c.new_classnumber)>0

                            union

                            select distinct
                            sc.new_customerid from new_sc_customerBase sc
                            left join LeadBase l on l.LeadId = sc.new_customerid
                            left join new_classBase c on c.new_classId = l.new_class
                            where l.new_school='{school}' and sc.statecode = 1  and charindex('{classnum}',c.new_classnumber)>0";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                log.Info(string.Format("ScreenCustomerDal 对像中,GetScreenCustomerBySchoolAndClassNum 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<dynamic>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据潜客ID与筛查ID查询筛查客户关系是否存在
        /// </summary>
        /// <param name="customerId">潜客ID</param>
        /// <param name="screenId">筛查ID</param>
        /// <returns></returns>
        public bool ExistsScreenCustomer(Guid customerId, Guid screenId)
        {
            return dapperHelper.ExecueWithoutTransaction<bool>(a =>
            {
                string sql = $@"select count(1) from dbo.new_sc_customerbase c 
	                            where c.new_scid='{screenId}' AND c.new_customerid = '{customerId}'
	                            and c.statecode=0";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                //log.Info(string.Format("ScreenCustomerDal 对像中,ExistsScreenCustomer 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                int result = a.QueryFirst<int>(sqlTemplate.RawSql);

                return result > 0 ? true : false;
            });
        }

        /// <summary>
        /// 根据潜客Id获取筛查客户信息
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> GetScreenCustomerByLeadId(Guid leadId)
        {
            if (leadId != Guid.Empty)
            {
                return new DapperHelper().ExecueWithoutTransaction(select =>
                {
                    string strsql = @"select new_scid as screenId,new_scidName,s.new_screenstate as screenstate from new_sc_customer
                                        inner join new_screen s on s.new_screenId=new_sc_customer.new_scid
                                      /**where**/";
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                    sqlBuilder.Where("new_customerid = @leadId", new { leadId = leadId });
                    sqlBuilder.Where("new_scid is not null");
                    return select.Query<dynamic>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                });
            }
            return null;
        }

        /// <summary>
        /// 根据潜客Id获取最近的筛查活动Id
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public Guid GetMaxScreenByLead(Guid leadId)
        {
            if (leadId != Guid.Empty)
            {
                return new DapperHelper().ExecueWithoutTransaction(select =>
                {
                    string strsql = @"select top 1 new_scid as screenId from new_sc_customer 
                                      left join new_screen on new_screen.new_screenId=new_sc_customer.new_scid 
                                      /**where**/ /**orderby**/";
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                    sqlBuilder.Where("new_customerid = @leadId", new { leadId = leadId });
                    sqlBuilder.Where("new_scid is not null");
                    sqlBuilder.OrderBy(" new_screen.CreatedOn desc ");
                    return select.ExecuteScalar<Guid>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                });
            }
            return Guid.Empty;
        }
    }
}
